package com.jit.base.webservice;

import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import com.jit.base.pojo.Department;
import com.jit.base.pojo.Groupinfo;
import com.jit.base.pojo.Oplog;
import com.jit.base.pojo.Resinfo;
import com.jit.base.pojo.Roleinfo;
import com.jit.base.pojo.Staff;
import com.jit.base.pojo.Structurebaseinfo;
import com.jit.base.pojo.Workgroup;
import com.jit.base.pojo.structure;
import com.jit.base.util.DbPoolConnection;
import com.jit.base.util.UUID;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Project: 统一用户管理系统 Filename: BaseWebSrvImp.java Company: JIT Northeast R & D
 * Author: tao_wang Copyright: Copyright (C) 2019, All rights reserved.
 * Description: 数据库客户端服务主键 Date 2011-10-23
 */

public class BaseWebService {

	private static Log log = LogFactory.getLog(BaseWebService.class);

	private DruidPooledConnection conn = null;

	private Statement stat = null;

	private PreparedStatement pre = null;

	private ResultSet rs = null;

	public BaseWebService() {
		DbPoolConnection cm = DbPoolConnection.getInstance();
		if (cm != null)
			try {
				this.conn = cm.getConnection();
			} catch (SQLException e) {
				log.error("取得获取连接异常", e);
			}
	}

	/**
	 * Description: 根据人员id获取人员信息
	 * 
	 * @param staffid	人员id
	 * @return Staff	人员信息对象
	 */
	public Staff getPersonInfo(String staffid) {
		if (this.conn == null) {
			return null;
		}
		if (staffid == null) {
			return null;
		}
		Staff staff = null;
		try {
			String sql = "select * from staff where staff_id=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				staff = getStaff(this.rs);
				staff.setJzList(getPartOfTimeListByStaffId(staffid));
				staff.setFgbmList(getPartOfDepartmentByStaffId(staffid));
				staff.setFgryList(getPartOfManagerByStaffId(staffid));
				staff.setAdmap(getAdMap(staffid));
			}
		} catch (Exception e) {
			log.error("根据人员id获取人员信息异常", e);
		} finally {
			closeRs();
		}
		return staff;
	}

	/**
	 * 通过组织机构节点code和组织机构节点类型获取组织机构节点对象
	 * 
	 * @param structurecode
	 *            组织机构节点coed
	 * @param elementType
	 *            组织机构节点类型
	 * @return structure
	 * 			组织结构节点对象
	 */
	public structure getStructureByCode(String structurecode, String elementType) {
		if (this.conn == null) {
			return null;
		}
		if ((structurecode == null) || (elementType == null)) {
			return null;
		}
		structure str = null;
		try {
			String sql = "select * from structure where structure_code=? and element_type=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, structurecode);
			this.pre.setString(2, elementType);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next())
				str = getStructure(this.rs);
		} catch (Exception e) {
			log.error("通过组织机构节点code和组织机构节点类型获取组织机构节点对象异常", e);
		} finally {
			closeRs();
		}

		return str;
	}

	/**
	 * Description: 根据人员id判断该人员是否为秘书
	 * 
	 * @param staffid
	 *            人员id
	 * @return 返回值为true、false
	 */
	public boolean isSec(String staffid) {
		if (this.conn == null) {
			return false;
		}
		if (staffid == null) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "select is_sec from staff where staff_id = ?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				Integer isSec = Integer.valueOf(this.rs.getInt("is_sec"));
				if (isSec.intValue() == 1) {
					result = true;
				}
			}
		} catch (Exception e) {
			log.error("根据人员id判断该人员是否为秘书异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 取得指定角色下的人员列表
	 * 
	 * @param appCode
	 *            应用标识码
	 * @param rolecode
	 *            角色代码
	 * @return 员列表，List中保存的是structure人员对象
	 */
	public List getPersonsByRoleCode(String appCode, String rolecode) {
		if (this.conn == null) {
			return null;
		}
		if ((appCode == null) && (rolecode == null)) {
			return null;
		}
		List list = null;
		try {
			String sql = "select s.* from structure s,roleinfo role,rolerelate relate where s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_code=? and role.role_code=? order by relate.id";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, rolecode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("取得指定角色下的人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据节点ID获取上级节点
	 * 
	 * @param elementid
	 *            节点id
	 * @return structure 
	 * 			     节点对象
	 */
	public structure getParentElementNode(String elementid) {
		if (this.conn == null) {
			return null;
		}
		if (elementid == null) {
			return null;
		}
		structure str = null;
		try {
			String sql = "select str2.* from structure str1, structure str2 where str1.element_parent_id = str2.element_id and str1.element_id =?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, elementid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				str = getStructure(this.rs);
			}
		} catch (Exception e) {
			log.error("根据节点ID获取上级节点信息异常", e);
		} finally {
			closeRs();
		}

		return str;
	}

	/**
	 * Description: 根据部门id获取部门的详细信息
	 * 
	 * @param depid
	 *            部门id
	 * @return 部门详细信息
	 * 				Department对象
	 */
	public Department getDepartment(String depid) {
		if (this.conn == null) {
			return null;
		}
		if (depid == null) {
			return null;
		}
		Department dep = null;
		try {
			String sql = "select * from department where department_id =?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, depid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				dep = getDepartment(this.rs);
				dep.setAdmap(getAdMap(depid));
			}
		} catch (Exception e) {
			log.error("根据部门id获取部门的详细信息异常", e);
		} finally {
			closeRs();
		}
		return dep;
	}

	/**
	 * Description: 根据组织机构节点id取得节点信息
	 * 
	 * @param elementid
	 *            组织机构节点id
	 * @return 组织机构节点详细信息
	 * 			structure对象
	 */
	public structure getStructure(String elementid) {
		if (this.conn == null) {
			return null;
		}
		if (elementid == null) {
			return null;
		}
		structure str = null;
		try {
			String sql = "select * from structure where element_id =?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, elementid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				str = getStructure(this.rs);
			}
		} catch (Exception e) {
			log.error("根据组织机构节点id取得节点信息异常", e);
		} finally {
			closeRs();
		}

		return str;
	}

	/**
	 * Description: 根据组织机构code获取指定机构下的领导列表
	 * 
	 * @param structureCode
	 *            组织机构code
	 * @return 人员列表
	 * 				List中保存的是structure人员对象
	 */
	public List getLeaders(String structureCode) {
		if (this.conn == null) {
			return null;
		}
		if (structureCode == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select a.* from structure a , staff b where a.element_id=b.staff_id and b.is_leader=1 and a.structure_code=? order by b.leader_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, structureCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("取得指定机构下的领导列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据部门id获取指定部门下的部门领导列表
	 * 
	 * @param depId
	 *            部门id
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List getDeptLeaders(String depId) {
		if (this.conn == null) {
			return null;
		}
		if (depId == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select a.* from structure a , staff b where a.element_id=b.staff_id and b.staff_type=1 and a.element_parent_id=? order by b.leader_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, depId);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据部门id获取指定部门下的部门领导列表", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据用户名和密码获取用户信息
	 * 
	 * @param username
	 *            用户名
	 * @param pwd
	 *            密码 （要求md5加密）
	 * @return map 用户信息集合
	 */
	public Map getAuthInfo(String username, String pwd) {
		if (this.conn == null) {
			return null;
		}
		if ((username == null) || (pwd == null)) {
			return null;
		}
		String sql = "select st1.element_id uuid,st1.element_name uname,st1.structure_code ucode,st1.structure_id usid,st1.element_allparent_id uaid,st2.element_id pid,st2.element_name pname,st2.structure_code pscode,st2.centercode pccode,staff.is_leader isleader,staff.is_sec isec,staff.office_tel otel,staff.handset handset,st2.element_parent_id ppid , dep.department_type dtype,st1.pwd pwd,st1.username username  from structure st1, structure st2, staff staff , department dep where st1.element_type='2' and st1.username=? and st1.pwd=?  and st1.element_parent_id = st2.element_id and staff.staff_id = st1.element_id and st2.element_id = dep.department_id";

		return getAuthInfo(sql, "", username, pwd);
	}

	/**
	 * Description: 根据用户DN获取用户信息
	 * 
	 * @param dn
	 *            用户DN
	 * @return
	 */
	public Map getAuthInfo(String dn) {
		if (this.conn == null) {
			return null;
		}
		if (dn == null)
			return null;
		String sql = "select st1.element_id uuid,st1.element_name uname,st1.structure_code ucode,st1.structure_id usid,st1.element_allparent_id uaid,st2.element_id pid,st2.element_name pname,st2.structure_code pscode,st2.centercode pccode,staff.is_leader isleader,staff.is_sec isec,staff.office_tel otel,staff.handset handset,st2.element_parent_id ppid , dep.department_type dtype,st1.pwd pwd, st1.username username from structure st1, structure st2, staff staff , department dep where st1.element_type='2' and st1.element_dn=? and st1.element_parent_id = st2.element_id and staff.staff_id = st1.element_id and st2.element_id = dep.department_id";

		return getAuthInfo(sql, dn, "", "");
	}

	/**
	 * Description: 根据应用code和人员id获取该应用下此人员有的角色列表
	 * 
	 * @param appCode
	 *            应用代码
	 * @param staffid
	 *            人员id
	 * @return 权限列表List中保存的是Roleinfo权限对象
	 */
	public List<Roleinfo> getRolesByStaffid(String appCode, String staffid) {
		if (this.conn == null) {
			return null;
		}
		if ((appCode == null) || (staffid == null)) {
			return null;
		}
		List list = null;
		try {
			String sql = "select role.role_id,role.role_desc,role.sys_id,role.role_name,role.role_order,role.role_code,role.sys_code from structure s,roleinfo role,rolerelate relate ,systeminfo sys where s.element_id=? and s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_id = sys.sys_id and sys.sys_code=? order by role.role_order";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.pre.setString(2, appCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Roleinfo role = new Roleinfo();
				role.setRoleId(rs.getString("role_id"));
				role.setRoleDesc(rs.getString("role_desc"));
				role.setSysId(rs.getString("sys_id"));
				role.setSysCode(rs.getString("sys_code"));
				role.setRoleName(rs.getString("role_name"));
				Double orderD = null;
				try {
					String order = rs.getString("role_order");
					orderD = Double.parseDouble(order);
				} catch (Exception e) {
					orderD = 0.00;
				}
				role.setRoleOrder(orderD);
				role.setRoleCode(rs.getString("role_code"));
				list.add(role);
			}
		} catch (Exception e) {
			log.error("根据应用code和人员id获取该应用下此人员有的角色列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据应用code和人员id获取该应用下此人员可操作的资源列表
	 * 
	 * @param appCode
	 *            应用代码
	 * @param staffid
	 *            人员id
	 * @return 权限列表List中保存的是Resinfo权限对象
	 */
	public List<Resinfo> getRightsByStaffid(String appCode, String staffid) {
		if (this.conn == null) {
			return null;
		}
		if ((appCode == null) || (staffid == null)) {
			return null;
		}
		List list = null;
		try {
			String sql = "select res.res_id,res.res_name,res.sys_id,res.res_order,res.res_code,res.structure_id,res.res_url from structure s,roleinfo role,resinfo res,rightinfo rig,rolerelate relate where s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_code=? and s.element_id=? and rig.role_id=role.role_id and rig.res_id=res.res_id order by res.res_order";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, staffid);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Resinfo res = new Resinfo();
				res.setResId(rs.getString("res_id"));
				res.setResName(rs.getString("res_name"));
				res.setSysId(rs.getString("sys_id"));
				Double orderD = null;
				try {
					String order = rs.getString("res_order");
					orderD = Double.parseDouble(order);
				} catch (Exception e) {
					orderD = 0.00;
				}
				res.setResOrder(orderD);
				res.setResCode(rs.getString("res_code"));
				res.setStructureId(rs.getString("structure_id"));
				res.setResUrl(rs.getString("res_url"));
				list.add(res);
			}
		} catch (Exception e) {
			log.error("根据应用code和人员id获取该应用下此人员可操作的资源列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 机构下第一层的部门（人员）注：不向下递归
	 * 
	 * @param structureid
	 *            机构id
	 * @param type
	 *            为查询类别，0为部门,1为人员，2为全部（包括部门人员）
	 * @return 机构列表，List中保存的是structure人员对象
	 */
	public List<structure> getAllElementByStructureId(String structureid,
			int type) {
		return getSonElementNode(structureid, type);
	}

	/**
	 * Description: 根据部门取得机构或部门下的部门或人员列表
	 * 
	 * @param elementid
	 *            父节点id
	 * @param type
	 *            查询类别，0为部门,1为人员，2为全部
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List<structure> getSonElementNode(String elementid, int type) {
		if (this.conn == null) {
			return null;
		}
		if (elementid == null) {
			return null;
		}
		List list = null;
		String sql = "";
		try {
			if (type == 3) {
				String whereStr = "union select * from structure t where t.element_type = 2  and t.element_id in (select staff_id from partoftime d where d.department_id = '"
						+ elementid + "')";
				sql = " select * from (select *  from structure t where t.element_type = 2 and t.element_parent_id = '"
						+ elementid
						+ "' "
						+ whereStr
						+ " ) order by element_Order ";
			} else {
				String elementType = " ";
				if (type == 0)
					elementType = " and element_type='1' ";
				if (type == 1) {
					elementType = " and element_type='2' ";
				}
				if(elementid.equals("0"))
				{
					sql = "select * from structure  where element_id=? "
							+ elementType + " order by element_order";
				}else {
					sql = "select * from structure  where element_parent_id=? "
							+ elementType + " order by element_order";
				}
				
			}
			this.pre = this.conn.prepareStatement(sql);
			if (type != 3) {
				this.pre.setString(1, elementid);
			}
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = new structure();
				str.setElementId(rs.getString("element_id"));
				str.setElementName(rs.getString("element_name"));
				str.setElementType(rs.getString("element_type"));
				str.setStructureId(rs.getString("structure_id"));
				str.setCenterCode(rs.getString("centercode"));
				str.setElementAllparentId(rs.getString("element_allparent_id"));
				str.setElementDn(rs.getString("element_dn"));
				Integer orderi = null;
				try {
					String order = rs.getString("element_order");
					orderi = Integer.parseInt(order);
				} catch (Exception e) {
					orderi = 0;
				}
				str.setElementOrder(orderi);
				str.setElementParentId(rs.getString("element_parent_id"));
				str.setStructureCode(rs.getString("structure_code"));
				str.setUsername(rs.getString("username"));
				str.setPwd(rs.getString("pwd"));
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据部门取得机构或部门下的部门或人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 
	 * Description: 根据部门取得机构或部门下的部门或人员列表支持模糊查询和过滤
	 * 
	 * @param elementid
	 *            父节点id
	 * @param type
	 *            查询类别，0为部门,1为人员，2为全部
	 * @param filterIds
	 *            过滤id
	 * @param vague
	 *            模糊查询节点名称
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List<structure> getSonElementNodeWithFilterAndVague(
			String elementid, int type, String filterIds, String vague) {
		if (this.conn == null) {
			return null;
		}
		if (elementid == null) {
			return null;
		}
		List list = null;
		String sql = "";
		try {
			String elementType = " ";
			if (type == 0)
				elementType = " and element_type='1' ";
			if (type == 1) {
				elementType = " and element_type='2' ";
			}
			sql = "select * from structure  where element_parent_id=? "
					+ elementType;

			if ((filterIds != null) && (!"".equals(filterIds.trim()))) {
				String[] arr = filterIds.split(",");
				String ids = "";
				for (int i = 0; i < arr.length; ++i) {
					if (i == 0)
						ids = ids + "'" + arr[i] + "'";
					else {
						ids = ids + ",'" + arr[i] + "'";
					}
				}
				sql = sql + " and element_id not in(" + ids + ") ";
			}

			if ((vague != null) && (!"".equals(vague.trim()))) {
				sql = sql + " and element_name like '%" + vague + "%' ";
			}

			sql = sql + " order by element_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, elementid);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = new structure();
				str.setElementId(this.rs.getString("element_id"));
				str.setElementName(this.rs.getString("element_name"));
				str.setElementType(this.rs.getString("element_type"));
				str.setStructureId(this.rs.getString("structure_id"));
				str.setCenterCode(this.rs.getString("centercode"));
				str.setElementAllparentId(this.rs
						.getString("element_allparent_id"));
				str.setElementDn(this.rs.getString("element_dn"));
				Integer orderi = null;
				try {
					String order = this.rs.getString("element_order");
					orderi = Integer.valueOf(Integer.parseInt(order));
				} catch (Exception e) {
					orderi = Integer.valueOf(0);
				}
				str.setElementOrder(orderi);
				str.setElementParentId(this.rs.getString("element_parent_id"));
				str.setStructureCode(this.rs.getString("structure_code"));
				str.setUsername(this.rs.getString("username"));
				str.setPwd(this.rs.getString("pwd"));
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据部门取得机构或部门下的部门或人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 取得部门下的所有部门或职员支持向下迭代
	 * 
	 * @param depId
	 *            单位标识
	 * @param type
	 *            类型（0代码部门,1代码职员,其他为全部）
	 * @return
	 */
	public List<structure> getDeptSubkey(String depId, int type) {
		if (this.conn == null) {
			return null;
		}
		if (depId == null) {
			return null;
		}
		List list = null;
		try {
			String elementType = " ";
			if (type == 0)
				elementType = " and element_type='1' ";
			if (type == 1) {
				elementType = " and element_type='2' ";
			}
			String sql = "select * from structure  where element_allparent_id like '%"
					+ depId + "%' " + elementType + " order by element_order";
			this.pre = this.conn.prepareStatement(sql);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = new structure();
				str.setElementId(rs.getString("element_id"));
				str.setElementName(rs.getString("element_name"));
				str.setElementType(rs.getString("element_type"));
				str.setStructureId(rs.getString("structure_id"));
				str.setCenterCode(rs.getString("centercode"));
				str.setElementAllparentId(rs.getString("element_allparent_id"));
				str.setElementDn(rs.getString("element_dn"));
				Integer orderi = null;
				try {
					String order = rs.getString("element_order");
					orderi = Integer.parseInt(order);
				} catch (Exception e) {
					orderi = 0;
				}
				str.setElementOrder(orderi);
				str.setElementParentId(rs.getString("element_parent_id"));
				str.setStructureCode(rs.getString("structure_code"));
				str.setUsername(rs.getString("username"));
				str.setPwd(rs.getString("pwd"));
				list.add(str);
			}
		} catch (Exception e) {
			log.error("取得部门下的所有部门或职员异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: Base对外提供日志操作服务接口，用于保存各应用下操作日志。
	 * 
	 * @param key
	 *            ：“appcode”，value：应用代码 key：“logtype”，value：操作类型
	 *            key：“loginfo”，value：日志信息 key：“username”，value：操作用户
	 *            key：“userid”，value：操作用户ID key：“userip”，value：操作用户IP
	 * @return 是否操作成功
	 */
	public boolean saveAppOPLog(String appcode, String logtype, String loginfo,
			String username, String userid, String userip) {
		boolean flag = false;
		String databaseType = getDatabseType();
		String optime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());
		try {
			String logId = new UUID().generate();
			String sql = "insert into oplog(id,appcode,logtype,loginfo,username,userid,userip,optime) values(?,?,?,?,?,?,?,to_date('"
					+ optime + "','yyyy-mm-dd hh24:mi:ss'))";

			if ("mysql".equalsIgnoreCase(databaseType)
					|| "gbase".equalsIgnoreCase(databaseType)) {
				sql = "insert into oplog(id,appcode,logtype,loginfo,username,userid,userip,optime) values(?,?,?,?,?,?,?,'"
						+ optime + "')";
			} else if ("sqlserver".equalsIgnoreCase(databaseType)) {
				sql = "insert into oplog(id,appcode,logtype,loginfo,username,userid,userip,optime) values(?,?,?,?,?,?,?,'"
						+ optime + "')";
			}

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, logId);
			this.pre.setString(2, appcode);
			this.pre.setString(3, logtype);
			this.pre.setString(4, loginfo);
			this.pre.setString(5, username);
			this.pre.setString(6, userid);
			this.pre.setString(7, userip);
			this.pre.execute();
			flag = true;
		} catch (Exception e) {
			log.error("记录应用操作日志失败异常" + e);
		} finally {
			closeRs();
		}
		return flag;
	}

	/**
	 * Description: Base对外提供日志查询服务接口，用于查询各应用下操作日志。
	 * 
	 * @param key
	 *            ：“appcode”，value：应用代码 key：“logtype”，value：操作类型
	 *            key：“loginfo”，value：日志信息 key：“username”，value：操作用户
	 *            key：“userid”，value：操作用户ID key：“userip”，value：操作用户IP
	 * @return 是否操作成功
	 */
	public List<Oplog> selectAppOPLog(String appcode, String logtype,
			String username, String userid, String userip, String starttime,
			String endtime) {
		boolean flag = false;
		List list = new ArrayList();
		try {
			String optime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date());
			String logId = new UUID().generate();
			String sql = "select * from oplog t where 1=1 ";
			String whereSql = "";

			if ((appcode != null) && (!"".equals(appcode)))
				whereSql = whereSql + "and t.appcode='" + appcode + "'";
			if ((logtype != null) && (!"".equals(logtype)))
				whereSql = whereSql + "and t.logtype='" + logtype + "'";
			if ((username != null) && (!"".equals(username)))
				whereSql = whereSql + "and t.username='" + username + "'";
			if ((userid != null) && (!"".equals(userid)))
				whereSql = whereSql + "and t.userid='" + userid + "'";
			if ((userip != null) && (!"".equals(userip)))
				whereSql = whereSql + "and t.userip='" + userip + "'";
			if ((starttime != null) && (!"".equals(starttime)))
				whereSql = whereSql
						+ "and to_char(t.optime,'yyyy-mm-dd hh24:mi:ss') >= '"
						+ starttime + "'";
			if ((endtime != null) && (!"".equals(endtime)))
				whereSql = whereSql
						+ "and to_char(t.optime,'yyyy-mm-dd hh24:mi:ss') <= '"
						+ endtime + "'";
			this.pre = this.conn.prepareStatement(sql + whereSql);
			this.rs = this.pre.executeQuery();
			while ((this.rs != null) && (this.rs.next())) {
				Oplog oplog = getOplog(this.rs);
				list.add(oplog);
			}
			flag = true;
		} catch (Exception e) {
			log.error("记录应用操作日志失败异常" + e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据部门id获取该部门下的所有人员
	 * 
	 * @param depId
	 *            单位标识
	 * @return List<structure> 部门下所有人员的节点对象集合
	 */
	public List<structure> getPersonsByDept(String depId) {
		if (this.conn == null) {
			return null;
		}
		if (depId == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select * from structure a where a.element_parent_id=? and a.element_type='2' order by a.element_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, depId);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据部门id获取该部门下的所有人员异常" + e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据秘书的人员id获取负责领导节点对象
	 * 
	 * @param staffid
	 *            秘书id
	 * @return structure 领导节点信息
	 */
	public structure fuzeLeader(String staffid) {
		if (this.conn == null) {
			return null;
		}
		if (staffid == null) {
			return null;
		}
		structure str = null;
		try {
			String sql = "select st.* from structure st ,staff sf where st.element_id=(select leader_id from Staff where staff_id=?)";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				str = getStructure(this.rs);
			}
		} catch (Exception e) {
			log.error("根据秘书的人员id获取负责领导节点对象异常", e);
		} finally {
			closeRs();
		}
		return str;
	}

	/**
	 * Description: 根据领导的人员id获取该领导的秘书
	 * 
	 * @param staffid
	 *            领导id
	 * @return 返回值为 List<structure>
	 */
	public List<structure> leaderMishu(String staffid) {
		if (this.conn == null) {
			return null;
		}
		if (staffid == null) {
			return null;
		}
		structure str = null;
		List<structure> l = new ArrayList<structure>();
		try {
			String sql = "select a.* from structure a , Staff b where a.element_id=b.staff_id and b.is_sec=1 and b.leader_id=? order by b.leader_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				str = getStructure(this.rs);
				l.add(str);
			}
		} catch (Exception e) {
			log.error("根据领导的人员id获取该领导的秘书异常", e);
		} finally {
			closeRs();
		}
		return l;
	}

	/**
	 * Description: 根据群组code取得群组人员列表
	 * 
	 * @param groupCode
	 *            群组代码
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List getPersonsByGroupCode(String groupCode) {
		if (groupCode == null)
			return null;
		try {
			String sqlmain = "select * from groupinfo where code=?";
			this.pre = this.conn.prepareStatement(sqlmain);
			this.pre.setString(1, groupCode);
			this.rs = this.pre.executeQuery();
			List result = new ArrayList();
			while (rs != null && rs.next()) {
				Groupinfo group = this.getGroupinfo(rs);
				String sql = "select s.* from structure s ,Staff p where ELEMENT_ID=p.STAFF_ID and "
						+ group.getSqlStr() + " order by ELEMENT_ORDER";
				PreparedStatement pre1 = this.conn.prepareStatement(sql);
				ResultSet rs1 = pre1.executeQuery();
				while (rs1 != null && rs1.next()) {
					structure s = getStructure(rs1);
					result.add(s);
				}
			}
			return result;

		} catch (Exception e) {
			log.error("根据群组code取得群组人员列表异常" + e);
			return null;
		}
	}

	/**
	 * Description: 根据应用code和资源code取得资源下的人员列表
	 * 
	 * @param appCode
	 *            应用标识码
	 * @param ResCode
	 *            资源代码
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List<structure> getPersonsByRight(String appCode, String ResCode) {
		if (this.conn == null) {
			return null;
		}
		if ((appCode == null) || (ResCode == null)) {
			return null;
		}
		List list = null;
		try {
			String sql = "select s.* from structure s,Roleinfo role,Resinfo res,Rightinfo rig,Rolerelate relate where s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_code=? and rig.role_id=role.role_id and rig.res_id=res.res_id and res.res_code=? order by s.element_order";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, ResCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据应用code和资源code取得资源下的人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据工作组code取得指定工作组下的人员列表
	 * 
	 * @param workgroupCode
	 *            工作组code
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List getPersonsByWorkgroupCode(String workgroupCode) {
		if (this.conn == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select s.* from structure s,Workgrouprelate relate,Workgroup  w where s.element_id=relate.relate_id and relate.workgroup_id=w.workgroup_id  and w.workgroup_code=? order by s.element_order";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, workgroupCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = this.getStructure(rs);
				list.add(str);
			}
		} catch (Exception e) {
			log.error("根据工作组code取得指定工作组下的人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据应用code取得资源列表
	 * 
	 * @param appCode
	 *            应用代码
	 * @return 资源列表List中保存的是Resinfo资源对象
	 */
	public List<Resinfo> getResources(String appCode) {
		if (this.conn == null) {
			return null;
		}
		if (appCode == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select res.* from Resinfo res,Systeminfo s where res.sys_id=s.sys_id and s.sys_code=? order by res.res_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Resinfo res = getResinfo(rs);
				list.add(res);
			}
		} catch (Exception e) {
			log.error("根据应用code取得资源列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 取得此角色下可操作资源列表
	 * 
	 * @param appCode
	 *            应用代码
	 * @param roleCode
	 *            角色代码
	 * @return 权限列表List中保存的是Resinfo权限对象
	 */
	public List<Resinfo> getRightsByRoleCode(String appCode, String roleCode) {
		if (this.conn == null) {
			return null;
		}
		if ((appCode == null) || (roleCode == null)) {
			return null;
		}
		List list = null;
		try {
			String sql = "select res.* from Resinfo res,Roleinfo role,Rightinfo rig  where rig.role_id=role.role_id and role.sys_code=? and role.role_code=?  and rig.res_id=res.res_id order by res.res_order ";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, roleCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Resinfo res = getResinfo(rs);
				list.add(res);
			}
		} catch (Exception e) {
			log.error("取得此角色下可操作资源列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据人员id判断该人员在指定应用下是否有指定权限（资源）
	 * 
	 * @param appCode
	 *            应用代码
	 * @param ResCode
	 *            权限代码
	 * @param staffid
	 *            人员id
	 * @return 返回值true、false
	 */
	public boolean hasRight(String appCode, String ResCode, String staffid) {
		if (this.conn == null) {
			return false;
		}
		if ((appCode == null) || (ResCode != null) || (staffid == null)) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "select count(s.element_id) as num from structure s,Roleinfo role,Resinfo res,Rightinfo rig,Rolerelate relate where s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_code=? and s.element_id=? and rig.role_id=role.role_id and rig.res_id=res.res_id and res.res_code=?";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, staffid);
			this.pre.setString(3, ResCode);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				Integer num = Integer.valueOf(this.rs.getInt("num"));
				if (num.intValue() == 1) {
					result = true;
				}
			}
		} catch (Exception e) {
			log.error("根据人员id判断该人员在指定应用下是否有指定权限（资源）异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 判断该指定人员是否在指定角色中
	 * 
	 * @param appCode
	 *            应用代码
	 * @param roleCode
	 *            角色代码
	 * @param staffid
	 *            人员id
	 * @return 返回值true、false
	 */
	public boolean hasRole(String appCode, String roleCode, String staffid) {
		if (this.conn == null)
			return false;
		if ((appCode == null) || (roleCode != null) || (staffid == null)) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "select count(s.element_id) as num from structure s,Roleinfo role,Rolerelate relate where s.element_id=? and s.element_id=relate.relate_id and relate.role_id=role.role_id and role.sys_code=? and role.role_code=?";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.pre.setString(2, appCode);
			this.pre.setString(3, roleCode);
			this.rs = this.pre.executeQuery();

			if (rs != null && rs.next()) {
				Integer num = Integer.valueOf(this.rs.getInt("num"));
				if (num.intValue() == 1) {
					result = true;
				}
			}
		} catch (Exception e) {
			log.error("判断该指定人员是否在指定角色中异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 判断指定人员是否为部门领导
	 * 
	 * @param staffid
	 *            人员id
	 * @return 返回值为true、false
	 */
	public boolean isDeptLeader(String staffid) {
		if (this.conn == null) {
			return false;
		}
		if (staffid == null) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "select * from staff where staff_id=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				Staff staff = getStaff(this.rs);
				if (staff.getStaffType().intValue() == 1) {
					result = true;
				}
			}
		} catch (Exception e) {
			log.error("判断指定人员是否为部门领导异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 判断指定人员是否为领导
	 * 
	 * @param staffid
	 *            人员id
	 * @return 返回值为true、false
	 */
	public boolean isLeader(String staffid) {
		if (this.conn == null) {
			return false;
		}
		if (staffid == null) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "select * from staff where staff_id=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				Staff staff = getStaff(this.rs);
				if (staff.getIsLeader() != null) {
					if (staff.getIsLeader().intValue() == 1) {
						result = true;
					}
				}
			}
		} catch (Exception e) {
			log.error("判断指定人员是否为领导异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 取得应用下角色列表
	 * 
	 * @param appCode
	 *            应用代码
	 * @return 资源列表  List中保存的是Roleinfo权限对象
	 */
	public List<Roleinfo> getRoles(String appCode) {
		if (this.conn == null) {
			return null;
		}
		if (appCode == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select * from Roleinfo role where role.sys_code=?  order by role.role_order";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Roleinfo res = getRoleinfo(rs);
				list.add(res);
			}
		} catch (Exception e) {
			log.error("取得应用下角色列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 修改用户登录密码
	 * 
	 * @param id
	 *            用户ID
	 * @param oldPwd
	 *            旧密码 （要求md5加密）
	 * @param newPwd
	 *            新密码 （要求md5加密）
	 * @return
	 */
	public boolean updateUserPwd(String id, String oldPwd, String newPwd) {
		if (this.conn == null)
			return false;
		if ((id == null) || (oldPwd == null) || (newPwd == null)) {
			return false;
		}
		boolean result = false;
		try {
			String sql = "update structure set pwd=?  where element_type='2' and element_id=? and pwd=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, newPwd);
			this.pre.setString(2, id);
			this.pre.setString(3, oldPwd);
			int i = this.pre.executeUpdate();
			if (i == 1)
				result = true;
		} catch (Exception e) {
			log.error("修改用户登录密码异常", e);
		} finally {
			closeRs();
		}
		return result;
	}

	/**
	 * Description: 取出element_id为id的一层数据字典子数据，如果id为空，取出所有element_type=1的数据字典数据
	 * 
	 * @param id 数据字典节点id
	 * 
	 * @return List<Structurebaseinfo> 数据字典节点对象集合
	 */
	public List<Structurebaseinfo> getStructurebaseinfoList(String id) {
		if (this.conn == null) {
			return null;
		}
		if (id == null) {
			return null;
		}

		List list = null;
		try {
			String sql = null;
			if ((id == null) || ("".equals(id))) {
				sql = "select * from Structurebaseinfo where element_type='1' order by element_order";
				this.pre = this.conn.prepareStatement(sql);
			} else {
				sql = "select *  from Structurebaseinfo where element_parent_id=? order by element_order";
				this.pre = this.conn.prepareStatement(sql);
				this.pre.setString(1, id);
			}
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Structurebaseinfo sbi = getStructurebaseinfo(rs);
				list.add(sbi);
			}
		} catch (Exception e) {
			log.error("取出element_id为id的所有子数据异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 根据角色code获取角色信息
	 * 
	 * @param code
	 *            角色代码
	 * @return 角色对象
	 */
	public Roleinfo getRoleinfoByCode(String code) {
		if (this.conn == null) {
			return null;
		}
		if (code == null) {
			return null;
		}

		Roleinfo ri = null;
		try {
			String sql = "select * from Roleinfo where role_code=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, code);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				ri = getRoleinfo(this.rs);
			}
		} catch (Exception e) {
			log.error("根据角色code获取角色信息异常", e);
		} finally {
			closeRs();
		}
		return ri;
	}

	/**
	 * Description: 根据工作组代码获取工作组
	 * 
	 * @param code
	 *            工作组代码
	 * @return Workgroup 工作组对象
	 */
	public Workgroup getWorkgroupByCode(String code) {
		if (this.conn == null) {
			return null;
		}
		if (code == null) {
			return null;
		}
		Workgroup wg = null;
		try {
			String sql = "select * from workgroup where workgroup_code=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, code);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				wg = getWorkgroup(this.rs);
			}
		} catch (Exception e) {
			log.error("获取工作组信息异常", e);
		} finally {
			closeRs();
		}
		return wg;
	}

	/**
	 * Description: 根据群组代码获取群组
	 * 
	 * @param code
	 *            群组代码
	 * @return Groupinfo 群组对象
	 */
	public Groupinfo getGroupinfoByCode(String code) {
		if (this.conn == null) {
			return null;
		}
		if (code == null) {
			return null;
		}
		Groupinfo gi = null;
		try {
			String sql = "select * from groupinfo where code=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, code);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				gi = getGroupinfo(this.rs);
			}
		} catch (Exception e) {
			log.error("获取工作组信息异常", e);
		} finally {
			closeRs();
		}
		return gi;
	}

	/**
	 * Description: 人员所属的所有父的父结点标识集合
	 * 
	 * @param staffid
	 *            人员标识
	 * @return 从下至上
	 */
	public List<String> getAllParentIdByStaffId(String elementid) {
		if (this.conn == null) {
			return null;
		}
		if (elementid == null) {
			return null;
		}
		List parentList = new ArrayList();
		try {
			String sql = "select * from structure where element_id=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, elementid);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				structure s = getStructure(this.rs);
				boolean flag = true;
				String parentid = s.getElementParentId();
				closeRs();
				while (flag && parentid != null && !elementid.equals("0")) {
					String sql_1 = "select element_id,element_type,element_parent_id,element_name from structure where element_id=?";
					pre = conn.prepareStatement(sql_1);
					pre.setString(1, parentid);
					rs = pre.executeQuery();
					if (rs != null && rs.next()) {
						String nextParent = rs.getString("element_id");
						String type = rs.getString("element_type");
						parentid = rs.getString("element_parent_id");
						parentList.add(nextParent);
						if (type.equals("0")) {
							flag = false;
						}
					} else {
						flag = false;
					}
					closeRs();
				}
			}
		} catch (Exception e) {
			log.error("人员所属的所有父的父结点标识集合异常", e);
		} finally {
			closeRs();
		}
		return parentList;
	}

	/**
	 * Description: 通过structureCode查找对应单位名称
	 * 
	 * @param structureCode
	 *            机构代码
	 * @return 单位名称
	 */
	public String getDeptNameFromStructureCode(String structurecode) {
		if (this.conn == null) {
			return null;
		}
		if (structurecode == null) {
			return null;
		}
		String name = null;
		try {
			String sql = "select element_name from structure where structure_code=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, structurecode);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				name = this.rs.getString("element_name");
			}
		} catch (Exception e) {
			log.error("通过structureCode查找对应单位名称异常", e);
		} finally {
			closeRs();
		}
		return name;
	}

	/**
	 * Description: 通过父节点部门的code查找子部门
	 * 
	 * @param parentbasecode
	 * @return
	 */
	public List<structure> getDepByParentBaseCode(String parentbasecode) {
		if (this.conn == null) {
			return null;
		}
		if (parentbasecode == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select * from structure where structure_code=? ";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, parentbasecode);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				structure s = getStructure(this.rs);
				closeRs();
				if (s != null) {
					String parentdepid = s.getElementId();
					sql = "select * from structure where element_parent_id=? and element_type = ?";
					this.pre = this.conn.prepareStatement(sql);
					this.pre.setString(1, parentdepid);
					this.pre.setDouble(2, 1);
					this.rs = this.pre.executeQuery();
					list = new ArrayList();
					while (rs != null && rs.next()) {
						structure st = getStructure(rs);
						list.add(st);
					}
				}
			}
		} catch (Exception e) {
			log.error("通过parentbasecode查找子单位异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * Description: 通过单位CODE获取下级单位信息，并将单位的交换中心标识也注入到返回对象中
	 * 
	 * @param depCode
	 *            单位CODE
	 * @param elementType
	 * @return List<structure> 组织机构节点对象
	 */
	public List<structure> getSonElementNodeOrCode(String depCode,
			String elementType) {
		if (conn == null)
			return null;

		if (depCode == null || elementType == null)
			return null;

		List<structure> tempList = null;

		try {
			String sql = "select * from structure where element_type = ? and structure_code = ?";
			pre = conn.prepareStatement(sql);
			pre.setString(1, elementType);
			pre.setString(2, depCode);
			rs = pre.executeQuery();

			String elementId = "";
			String centerCode = "";
			List<structure> strList = new ArrayList<structure>();
			while (rs != null && rs.next()) {
				structure str = getStructure(rs);
				strList.add(str);
			}
			if (strList != null && strList.size() == 1) {
				structure str = strList.get(0);
				elementId = str.getElementId();
				centerCode = str.getCenterCode();
				if (centerCode == null || centerCode.equals("")) {
					boolean result = true;
					while (result) {
						this.closeRs();
						String sql2 = "select * from structure where element_id=?";
						pre = conn.prepareStatement(sql2);
						pre.setString(1, str.getElementParentId());
						rs = pre.executeQuery();
						if (rs != null && rs.next()) {
							str = getStructure(rs);
							if (str == null)
								break;
							String tempCcod = str.getCenterCode();
							if (tempCcod != null && !tempCcod.equals("")) {
								centerCode = tempCcod;
								result = false;
							}
						} else {
							break;
						}
					}
				}
			} else {
				return null;
			}
			this.closeRs();
			String sql_2 = "select * from structure  where element_parent_id=? and element_type='1'  order by element_order";
			pre = conn.prepareStatement(sql_2);
			pre.setString(1, elementId);
			rs = pre.executeQuery();
			int j = 0;
			while (rs != null && rs.next() && centerCode != null
					&& !centerCode.equals("")) {
				if (j == 0)
					tempList = new ArrayList<structure>();
				structure str = getStructure(rs);
				str.setCenterCode(centerCode);
				tempList.add(str);
				j++;
			}
		} catch (Exception e) {
			log.error("通过单位CODE获取下级单位信息异常", e);
		} finally {
			closeRs();
		}
		return tempList;
	}

	public void close() {
		try {
			if (this.rs != null)
				this.rs.close();
		} catch (Exception e) {
			log.error("关闭结果集异常", e);
		}
		try {
			if (this.stat != null)
				this.stat.close();
		} catch (Exception e) {
			log.error("关闭查询异常", e);
		}
		try {
			if (this.pre != null)
				this.pre.close();
		} catch (Exception e) {
			log.error("关闭查询异常", e);
		}
		try {
			if (this.conn != null)
				this.conn.close();
		} catch (Exception e) {
			log.error("关闭连接异常", e);
		}
	}

	/**
	 * 获取人员兼职部门
	 * 
	 * @param staffId
	 *            人员标识
	 * @return List 列表 structure {@link [com.jit.base.pojo.structure]}
	 * @throws Exception
	 */
	public List<structure> getPartOfTimeListByStaffId(String staffId)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		if (staffId == null) {
			throw new Exception("人员标识为null");
		}
		List list = new ArrayList();
		try {
			String sql = "select * from structure t where t.element_id in (select department_id from partoftime t where t.staff_id =?)";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffId);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				structure s = getStructure(rs);
				list.add(s);
			}
		} catch (Exception e) {
			log.error("通过staffId获取人员兼职部门异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 获取人员分管部门列表
	 * 
	 * @param staffId
	 *            人员标识
	 * @return List 列表 structure {@link [com.jit.base.pojo.structure]}
	 * @throws Exception
	 */
	public List<structure> getPartOfDepartmentByStaffId(String staffId)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		if (staffId == null) {
			throw new Exception("人员标识为null");
		}
		List list = new ArrayList();
		String databaseType = getDatabseType();
		try {
			String sql = "select * from structure t , partofmanager p where  (INSTR( p.department_ids,t.element_id)>0) and p.currentstaff_id =?";// oracle,mysql,gbase
			if ("sqlserver".equalsIgnoreCase(databaseType)) {
				sql = "select * from structure t , partofmanager p where  p.department_ids like '%'+t.element_id+'%' and p.currentstaff_id =?";
			}
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffId);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				structure s = getStructure(rs);
				list.add(s);
			}
		} catch (Exception e) {
			log.error("通过staffId获取人员分管部门列表", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 获取人员分管人员列表
	 * 
	 * @param staffId
	 *            人员标识
	 * @return List 列表 structure {@link [com.jit.base.pojo.structure]}
	 * @throws Exception
	 */
	public List<structure> getPartOfManagerByStaffId(String staffId)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		if (staffId == null) {
			throw new Exception("人员标识为null");
		}
		List list = new ArrayList();
		String databaseType = getDatabseType();
		try {
			String sql = "select * from structure t , partofmanager p where  (INSTR( p.staff_ids,t.element_id)>0) and p.currentstaff_id =?";
			if ("sqlserver".equalsIgnoreCase(databaseType)) {
				sql = "select * from structure t , partofmanager p where  p.staff_ids like '%'+t.element_id+'%' and p.currentstaff_id =?";
			}
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffId);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				structure s = getStructure(rs);
				list.add(s);
			}
		} catch (Exception e) {
			log.error("通过staffId获取人员分管人员列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 通过elementid获取扩展属性列表
	 * 
	 * @param elementid
	 *            节点id
	 * @return Map 扩展属性 key 名称 value 值
	 * @throws Exception
	 */
	public Map getAdMap(String id) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为空");
		}
		if (id == null) {
			throw new Exception("标识为null");
		}
		Map map = new HashMap();
		try {
			String sql = "select t.adcode,t.adtype,a.strvalue,a.datevalue,a.numvalue,a.clobvalue from attributevalue a,attributedefined t where a.adid=t.adid and a.elementid=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, id);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				String adtype = rs.getString(2);
				if ("1".equals(adtype) || "3".equals(adtype)
						|| "5".equals(adtype) || "6".equals(adtype))
					map.put(rs.getString(1), rs.getString(3));
				if ("2".equals(adtype))
					map.put(rs.getString(1), rs.getString(6));
				if ("4".equals(adtype))
					map.put(rs.getString(1), rs.getString(4));

			}
		} catch (Exception e) {
			log.error("通过elementid获取扩展属性列表异常", e);
		} finally {
			closeRs();
		}
		return map;
	}

	/**
	 * 验证某人是否具有相关属性的特定值
	 * 
	 * @param sid
	 *            人员id
	 * @param attribute
	 *            指定的基本属性
	 * @param value
	 *            指定属性的值
	 * @return List 列表 staff
	 * @throws Exception
	 * @throws Exception
	 */
	public boolean validateStaffOfProperty(String sid, String property,
			String value) throws Exception {
		boolean b = false;
		if (this.conn == null)
			throw new Exception("数据库连接为null");
		try {
			String sql = "select * from staff where " + property + " = '"
					+ value + "' and staff_id = '" + sid + "'";
			this.pre = this.conn.prepareStatement(sql);
			this.rs = this.pre.executeQuery();
			if (rs != null && rs.next()) {
				b = true;
			}
		} catch (Exception e) {
			log.error("验证某人是否具有相关属性的特定值", e);
		} finally {
			closeRs();
		}
		return b;
	}

	/**
	 * Description: 获取机构下所有的部门或人员或全部部门和人员包括子级别下的（递归）
	 * 
	 * @param structureid
	 *            机构id
	 * @param type
	 *            为查询类别，0为部门,1为人员，2为全部,3为兼职下的全部
	 * @return 机构列表，List中保存的是structure人员对象
	 */
	public List<structure> getIterElementByStructureId(String structureid,
			int type) {
		PreparedStatement pre1 = null;
		ResultSet rs1 = null;

		PreparedStatement pre2 = null;
		ResultSet rs2 = null;

		if (this.conn == null) {
			return null;
		}
		if (structureid == null) {
			return null;
		}
		List list = null;
		String sql = "";
		try {
			if (type == 3) {
				sql = " select * from structure t where t.element_type != 0 and t.element_allparent_id like '%"
						+ structureid
						+ "%' and t.element_parent_id !='-1' order by element_Order ";
			} else {
				String elementType = " ";
				if (type == 0)
					elementType = " and element_type='1' ";
				if (type == 1) {
					elementType = " and element_type='2' ";
				}
				sql = "select * from structure  where element_allparent_id like ? "
						+ elementType
						+ " and element_parent_id !='-1'  order by element_order";
			}
			this.pre = this.conn.prepareStatement(sql);
			if (type != 3) {
				this.pre.setString(1, "%" + structureid + "%");
			}
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				structure str = new structure();
				str.setElementId(this.rs.getString("element_id"));
				str.setElementName(this.rs.getString("element_name"));
				str.setElementType(this.rs.getString("element_type"));
				str.setStructureId(this.rs.getString("structure_id"));
				str.setCenterCode(this.rs.getString("centercode"));
				str.setElementAllparentId(this.rs
						.getString("element_allparent_id"));
				str.setElementDn(this.rs.getString("element_dn"));
				Integer orderi = null;
				try {
					String order = this.rs.getString("element_order");
					orderi = Integer.valueOf(Integer.parseInt(order));
				} catch (Exception e) {
					orderi = Integer.valueOf(0);
				}
				str.setElementOrder(orderi);
				str.setElementParentId(this.rs.getString("element_parent_id"));
				str.setStructureCode(this.rs.getString("structure_code"));
				str.setUsername(this.rs.getString("username"));
				str.setPwd(this.rs.getString("pwd"));
				list.add(str);
			}
			if (type == 3) {
				String stsql = "select id,staff_id,department_id from partoftime d where d.department_id in (select element_id from structure  where element_allparent_id like '%"
						+ structureid
						+ "%' and element_type='1' and element_parent_id !='-1' )";
				pre1 = this.conn.prepareStatement(stsql);
				rs1 = pre1.executeQuery();
				while (rs1 != null && rs1.next()) {
					String sssql = "select * from structure t where t.element_id ='"
							+ rs1.getString(2) + "'";
					pre2 = conn.prepareStatement(sssql);
					rs2 = pre2.executeQuery();
					while (rs2 != null && rs2.next()) {
						structure str = new structure();
						str.setElementId(rs2.getString("element_id"));
						str.setElementName(rs2.getString("element_name"));
						str.setElementType(rs2.getString("element_type"));
						str.setStructureId(rs2.getString("structure_id"));
						str.setCenterCode(rs2.getString("centercode"));
						str.setElementAllparentId(rs2
								.getString("element_allparent_id"));
						str.setElementDn(rs2.getString("element_dn"));
						Integer orderi = null;
						try {
							String order = rs2.getString("element_order");
							orderi = Integer.parseInt(order);
						} catch (Exception e) {
							orderi = 0;
						}
						str.setElementOrder(orderi);
						str.setElementParentId(rs1.getString(3));
						str.setStructureCode(rs2.getString("structure_code"));
						str.setUsername(rs2.getString("username"));
						str.setPwd(rs2.getString("pwd"));
						list.add(str);
					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error("根据部门取得机构或部门下的部门或人员列表异常", e);
		} finally {
			closeRs();
			try {
				if (pre1 != null)
					pre1.close();
				if (pre2 != null)
					pre2.close();
			} catch (Exception e) {
				log.error("关闭查询异常", e);
			}
			try {
				if (rs1 != null)
					rs1.close();
				if (rs2 != null)
					rs2.close();
			} catch (Exception e) {
				log.error("关闭查询异常", e);
			}
		}
		return list;
	}

	/**
	 * 获取一个部门下拥有某一角色的人员集合
	 * 
	 * @param departmentId
	 *            部门ID
	 * @param roleCode
	 *            角色Code
	 * @return List 列表 staff
	 * @throws Exception
	 * @throws Exception
	 */
	public List<Staff> getStaffListByDepartmentIdAndRoleId(String departmentId,
			String roleCode) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		List list = null;
		try {
			String sql = "select s.* from staff s,roleinfo role,rolerelate relate where s.staff_id=relate.relate_id and  s.staff_id in(select element_id from structure s where s.element_parent_id = ? and s.element_type='2') and relate.role_id=role.role_id and role.role_code=?  order by role.role_order";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, departmentId);
			this.pre.setString(2, roleCode);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Staff st = getStaff(rs);
				list.add(st);
			}
		} catch (Exception e) {
			log.error("获取一个部门下拥有某一角色的人员集合列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 获取一个部门下拥有某一资源的人员集合
	 * 
	 * @param departmentId
	 *            部门ID
	 * @param roleId
	 *            资源ID
	 * @return List 列表 staff
	 * @throws Exception
	 * @throws Exception
	 */
	public List<Staff> getStaffListByDepartmentIdAndResId(String departmentId,
			String resId) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		List list = null;
		try {
			String sql = "select * from staff t where t.staff_id in (select distinct  staff_id  from staff s, roleinfo role, rolerelate relate where s.staff_id = relate.relate_id and s.staff_id in (select element_id from structure s where s.element_parent_id = ? and s.element_type = '2') and relate.role_id = role.role_id and role.role_id in (select role_id from rightinfo r where r.res_id in (select tt.res_id from resinfo tt where tt.res_code = ?))) ";

			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, departmentId);
			this.pre.setString(2, resId);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Staff st = getStaff(rs);
				list.add(st);
			}
		} catch (Exception e) {
			log.error("获取一个部门下拥有某一资源的人员集合异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 获取群组集合
	 * 
	 * @param
	 * @return List 列表 Groupinfo
	 * @throws Exception
	 */
	public List<Groupinfo> getGroupinfos() throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		List list = null;
		try {
			String sql = "select id,type_name,group_order,type_value,group_desc,sql_str,count_type,group_name,code from Groupinfo order by group_order";

			this.pre = this.conn.prepareStatement(sql);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Groupinfo workgroup = getGroupinfo(rs);
				list.add(workgroup);
			}
		} catch (Exception e) {
			log.error("获取一个部门下拥有某一资源的人员集合异常", e);
		} finally {
			closeRs();
		}
		return list;
	}

	

	// qm 2014-03-13 add many interfaces sychronize with webservice

	/**
	 * Description: 获取某人所在的工作组
	 * 
	 * @param staffId
	 *            用户ID
	 * @return 工作组列表，List中保存的是Workgroup人员对象
	 */
	public List<Workgroup> getWorkgroupByStaffId(String staffId)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		List list = null;
		String sql = "select w.* from Workgroup w,Workgrouprelate r where w.workgroup_Id=r.workgroup_Id and r.relate_Id=? order by w.workgroup_Order_Id";
		this.pre = this.conn.prepareStatement(sql);
		this.pre.setString(1, staffId);
		this.rs = this.pre.executeQuery();
		list = new ArrayList();
		while (rs != null && rs.next()) {
			Workgroup workgroup = workgroup(rs);
			list.add(workgroup);
		}
		return list;
	}

	/**
	 * Description: 获取某机构下的工作组
	 * 
	 * @param structureId
	 *            机构id
	 * @return 工作组列表，List中保存的是Workgroup人员对象
	 */
	public List<Workgroup> getWorkgroupByStructureId(String structureId)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		List list = null;
		String sql = "select * from Workgroup where structure_Id=? order by workgroup_Order_Id";
		this.pre = this.conn.prepareStatement(sql);
		this.pre.setString(1, structureId);
		this.rs = this.pre.executeQuery();
		list = new ArrayList();
		while (rs != null && rs.next()) {
			Workgroup workgroup = workgroup(rs);
			list.add(workgroup);
		}
		return list;

	}

	/**
	 * Description: 是否在指定群组中
	 * 
	 * @param groupCode
	 *            群组代码
	 * @param staffid
	 *            人员id
	 * @return 返回值true、false
	 */
	public boolean hasGroup(String groupCode, String staffid) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		String sql1 = "select sql_str from Groupinfo where code=?";
		this.pre = this.conn.prepareStatement(sql1);
		this.pre.setString(1, groupCode);
		this.rs = this.pre.executeQuery();
		String partSql = "";
		while (rs != null && rs.next()) {
			partSql = rs.getString("sql_str");
		}
		if (partSql != null && partSql.length() > 0) {

			String sql = "select count(STAFF_ID) from Staff where STAFF_ID= ? and "
					+ partSql;
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, staffid);
			this.rs = this.pre.executeQuery();
			while (rs != null && rs.next()) {
				int count = rs.getInt(1);
				if (count > 0)
					return true;
			}
		}
		return false;
	}

	/**
	 * Description: 修改用户登录账户
	 * 
	 * @param id
	 *            用户ID
	 * @param username
	 *            新登录帐号
	 * @param pwd
	 *            新密码 （要求md5加密）
	 * @return -1 用户不存在,-2新登录帐号已经存在，>0更新操作成功
	 */
	public int updateUserLoginName(String id, String username, String pwd)
			throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}

		String hql = "select * from structure s where element_Type='2' and element_Id = ? ";

		this.pre = this.conn.prepareStatement(hql);
		this.pre.setString(1, id);
		this.rs = this.pre.executeQuery();
		while (rs != null && rs.next()) {
			if (!rs.first()) {
				return -1;
			}
		}

		String otherSql = "select * from structure s where element_Type='2' and username =?";
		this.pre = this.conn.prepareStatement(otherSql);
		this.pre.setString(1, username);
		this.rs = this.pre.executeQuery();
		if (rs != null && rs.next()) {
			return -2;
		}

		String updateSql = "update structure set username=?  where element_type='2' and element_id=? and pwd=?";
		this.pre = this.conn.prepareStatement(updateSql);
		this.pre.setString(1, username);
		this.pre.setString(2, id);
		this.pre.setString(3, pwd);
		return this.pre.executeUpdate();
	}

	/**
	 * 2010-03-09 需求想获取机构树但是有些结点要根据条件过滤掉
	 * Description:根据指定的where子句取得机构或部门下的部门或人员列表
	 * 
	 * @param elementid
	 *            父节点id
	 * @param type
	 *            查询类别，0为部门,1为人员，2为全部
	 * @param whereSql
	 *            注意whereSql必须是”and “开头，如：and Structure_Id='' and
	 *            element_Parent_Id='' ;必须附合hql标准,字段名称与structure对象内的一致
	 * @return 人员列表，List中保存的是structure人员对象
	 */
	public List<structure> getSonElementNode(String elementid, int type,
			String whereSql) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为null");
		}
		if (elementid == null)
			return null;
		try {
			String elementType = " ";
			if (type == 0)
				elementType = " and element_Type='1' ";
			if (type == 1)
				elementType = " and element_Type='2' ";
			String hql = "select * from structure  where element_Parent_Id=? "
					+ elementType + " " + whereSql + " order by element_Order";
			this.pre = this.conn.prepareStatement(hql);
			this.pre.setString(1, elementid);
			this.rs = this.pre.executeQuery();
			List list = new ArrayList();
			while (rs != null && rs.next()) {
				structure one = getStructure(rs);
				list.add(one);
			}
			return list;
		} catch (Exception e) {
			log.error(e);
			throw e;
		}

	}

	// 2014-07-30 yulong_geng

	/**
	 * 取得指定扩展属性为特定值的人员列表
	 * 
	 * @param property
	 *            扩展属性
	 * @param value
	 *            指定属性的值
	 * @return List 列表 staff
	 * @throws Exception
	 * @throws Exception
	 */
	public List<Staff> getStaffListByAd(String property, String value)
			throws Exception {
		List list = new ArrayList();
		if (this.conn == null)
			throw new Exception("数据库连接为null");
		try {
			String sql = "select ad.adid,ad.adtype from ATTRIBUTEDEFINED ad.admodel = '1' ad where ad.adcode='"
					+ property + "'";
			this.pre = this.conn.prepareStatement(sql);
			this.rs = this.pre.executeQuery();
			String adid = "";
			String adtype = "";
			if (rs != null && rs.next()) {
				adid = rs.getString(1);
				adtype = rs.getString(2);
			}
			String wheresql = "";
			if ("1".equals(adtype) || "3".equals(adtype) || "5".equals(adtype)
					|| "6".equals(adtype))
				wheresql = "a.strvalue = ?";
			if ("2".equals(adtype))
				wheresql = "a.clobvalue = ?";
			if ("4".equals(adtype))
				wheresql = "a.datevalue = ?";

			sql = "select a.elementid from ATTRIBUTEVALUE a where a.adid= ? and ";
			sql += wheresql;
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, adid);
			this.pre.setString(2, value);
			this.rs = this.pre.executeQuery();
			List eleids = new ArrayList();
			while (rs != null && rs.next()) {
				eleids.add(rs.getString(1));
			}

			for (int i = 0; i < eleids.size(); i++) {
				Staff staff = getPersonInfo(eleids.get(i).toString());
				list.add(staff);
			}
		} catch (Exception e) {
			log.error("取得指定基本属性为特定值的人员列表", e);
		} finally {
			closeRs();
		}
		return list;
	}

	/**
	 * 取得指定基本属性（不支持扩展属性）为特定值的人员列表
	 * 
	 * @param depId
	 *            部门Id
	 * @param map
	 *            key为基本属性名称 value为值
	 * @return List 列表 staff
	 * @throws Exception
	 * @throws Exception
	 */
	public List<Staff> getStaffListByProperty(String depId, Map map)
			throws Exception {

		List list = null;
		if (this.conn == null)
			throw new Exception("数据库连接为null");
		try {
			if (map != null && map.size() > 0) {
				list = new ArrayList();
				String sql = "select s.* from staff s where 1=1 ";
				if (depId != null && !"".equals(depId)) {
					sql = "select s.* from staff s ,structure t where s.staff_id = t.element_id and t.element_parent_id = '"
							+ depId + "' ";
				}
				String whereSql = "";
				Iterator iter = map.entrySet().iterator();
				while (iter.hasNext()) {
					Entry e = (Entry) iter.next();
					String property = (String) e.getKey();
					String value = (String) e.getValue();
					whereSql += "and " + "s." + property + " = '" + value
							+ "' ";
				}
				sql += whereSql;
				this.pre = this.conn.prepareStatement(sql);
				this.rs = this.pre.executeQuery();
				while (rs != null && rs.next()) {
					Staff staff = getStaff(rs);
					list.add(staff);
				}
			}

		} catch (Exception e) {
			log.error("取得指定基本属性为特定值的人员列表", e);
		} finally {
			closeRs();
		}
		return list;
	}

	// -------------------以下为私有方法------------------------

	private void closeRs() {
		try {
			if (this.rs != null)
				this.rs.close();
		} catch (Exception e) {
			log.error("关闭结果集异常", e);
		}
		try {
			if (this.stat != null)
				this.stat.close();
		} catch (Exception e) {
			log.error("关闭查询异常", e);
		}
		try {
			if (this.pre != null)
				this.pre.close();
		} catch (Exception e) {
			log.error("关闭查询异常", e);
		}
	}

	// 从结果集中获取部门信息
	private Department getDepartment(ResultSet rs) throws Exception {
		Department dep = new Department();
		dep.setDepartmentActr(rs.getString("department_actr"));
		dep.setDepartmentDesc(rs.getString("department_desc"));
		dep.setDepartmentFullname(rs.getString("department_fullname"));
		dep.setDepartmentId(rs.getString("department_id"));
		dep.setDepartmentName(rs.getString("department_name"));
		dep.setDepartmentNo(rs.getString("department_no"));
		dep.setDepartmentOrder(rs.getString("department_order"));
		dep.setDepartmentSimplename(rs.getString("department_simplename"));
		dep.setDepartmentTel(rs.getString("department_tel"));
		dep.setDepartmentType(rs.getString("department_type"));
		dep.setDepartmentUrl(rs.getString("department_url"));
		dep.setStructureId(rs.getString("structure_id"));
		dep.setIsShowTree(rs.getString("is_showtree"));
		return dep;
	}

	// 从结果集中获取STR信息
	private structure getStructure(ResultSet rs) throws Exception {
		structure str = new structure();
		str.setElementId(rs.getString("element_id"));
		str.setElementName(rs.getString("element_name"));
		str.setElementType(rs.getString("element_type"));
		str.setStructureId(rs.getString("structure_id"));
		str.setCenterCode(rs.getString("centercode"));
		str.setElementAllparentId(rs.getString("element_allparent_id"));
		str.setElementDn(rs.getString("element_dn"));
		Integer orderi = null;
		try {
			String order = rs.getString("element_order");
			orderi = Integer.valueOf(Integer.parseInt(order));
		} catch (Exception e) {
			orderi = Integer.valueOf(0);
		}
		str.setElementOrder(orderi);
		str.setElementParentId(rs.getString("element_parent_id"));
		str.setStructureCode(rs.getString("structure_code"));
		str.setUsername(rs.getString("username"));
		str.setPwd(rs.getString("pwd"));
		return str;
	}

	// 从结果集中获取oplog信息
	private Oplog getOplog(ResultSet rs) throws Exception {
		Oplog oplog = new Oplog();
		oplog.setAppcode(rs.getString("appcode"));
		oplog.setId(rs.getString("id"));
		oplog.setLoginfo(rs.getString("loginfo"));
		oplog.setLogtype(rs.getString("logtype"));
		oplog.setOptime(rs.getString("optime"));
		oplog.setUserid(rs.getString("userid"));
		oplog.setUserip(rs.getString("userip"));
		oplog.setUsername(rs.getString("username"));
		return oplog;
	}

	// 从结果集中获取用户信息
	private Staff getStaff(ResultSet rs) throws Exception {
		Staff staff = new Staff();
		staff.setStaffId(rs.getString("staff_id"));
		staff.setOfficeTel(rs.getString("office_tel"));
		staff.setDutyLevel(rs.getString("duty_level"));
		staff.setNativePlace(rs.getString("native_place"));
		Integer stypei = null;
		try {
			String stype = rs.getString("staff_type");
			stypei = Integer.valueOf(Integer.parseInt(stype));
		} catch (Exception e) {
			stypei = Integer.valueOf(0);
		}
		staff.setStaffType(stypei);
		staff.setFinishSchool(rs.getString("finish_school"));
		staff.setDutyId(rs.getString("duty_id"));
		staff.setHomeTel(rs.getString("home_tel"));
		staff.setDutyLevelId(rs.getString("duty_level_id"));
		staff.setFax(rs.getString("fax"));
		staff.setSpeciality(rs.getString("speciality"));
		staff.setXingbie(rs.getString("xingbie"));
		staff.setHandset(rs.getString("handset"));
		staff.setWorkActr(rs.getString("work_actr"));
		staff.setInstitute(rs.getString("institute"));
		Integer isSeci = null;
		try {
			String isSec = rs.getString("is_sec");
			isSeci = Integer.valueOf(Integer.parseInt(isSec));
		} catch (Exception e) {
			isSeci = Integer.valueOf(0);
		}
		staff.setIsSec(isSeci);
		staff.setPhoto(rs.getBytes("photo"));
		staff.setDegree(rs.getString("degree"));
		staff.setStructureId(rs.getString("structure_id"));
		staff.setLeaderOrder(rs.getString("leader_order"));
		staff.setStaffName(rs.getString("staff_name"));
		staff.setBirthday(rs.getString("birthday"));
		staff.setBirthActr(rs.getString("birth_actr"));
		staff.setWorkBeginTime(rs.getString("work_begin_time"));
		staff.setKnowledge(rs.getString("knowledge"));
		staff.setDegreeId(rs.getString("degree_id"));
		Integer statei = null;
		try {
			String state = rs.getString("staff_state");
			statei = Integer.valueOf(Integer.parseInt(state));
		} catch (Exception e) {
			statei = Integer.valueOf(0);
		}
		staff.setStaffState(statei);
		staff.setRemark(rs.getString("remark"));
		staff.setTitleTechnical(rs.getString("title_technical"));
		Integer isLeaderi = null;
		try {
			String isLeader = rs.getString("is_leader");
			isLeaderi = Integer.valueOf(Integer.parseInt(isLeader));
		} catch (Exception e) {
			isLeaderi = Integer.valueOf(0);
		}
		staff.setIsLeader(isLeaderi);
		staff.setWorkDispatch(rs.getString("work_dispatch"));
		staff.setJionCcpTime(rs.getString("jion_ccp_time"));
		staff.setKnowledgeId(rs.getString("knowledge_id"));
		staff.setLeaderId(rs.getString("leader_id"));
		staff.setEmail(rs.getString("email"));
		staff.setNation(rs.getString("nation"));
		staff.setTitleTechnicalId(rs.getString("title_technical_id"));
		staff.setResume(rs.getString("resume"));
		staff.setDuty(rs.getString("duty"));
		staff.setUmsid(rs.getString("umsid"));
		staff.setMiji(rs.getString("miji"));
		staff.setResperson(rs.getString("resperson"));
		staff.setIsShowTree(rs.getString("is_showtree"));

		return staff;
	}

	private Resinfo getResinfo(ResultSet rs) throws Exception {
		Resinfo res = new Resinfo();
		res.setResId(rs.getString("res_id"));
		res.setResName(rs.getString("res_name"));
		res.setSysId(rs.getString("sys_id"));
		res.setResOrder(Double.valueOf(rs.getDouble("res_order")));
		res.setResCode(rs.getString("res_code"));
		res.setStructureId(rs.getString("structure_id"));
		res.setResUrl(rs.getString("res_url"));
		return res;
	}

	private Roleinfo getRoleinfo(ResultSet rs) throws Exception {
		Roleinfo role = new Roleinfo();
		role.setRoleId(rs.getString("role_id"));
		role.setRoleDesc(rs.getString("role_desc"));
		role.setSysId(rs.getString("sys_id"));
		role.setRoleName(rs.getString("role_name"));
		role.setRoleOrder(Double.valueOf(rs.getDouble("role_order")));
		role.setRoleCode(rs.getString("role_code"));
		role.setSysCode(rs.getString("sys_code"));
		return role;
	}

	private Workgroup getWorkgroup(ResultSet rs) throws Exception {
		Workgroup wg = new Workgroup();
		wg.setStructureId(rs.getString("structure_id"));
		wg.setWorkgroupCode(rs.getString("workgroup_code"));
		wg.setWorkgroupDesc(rs.getString("workgroup_desc"));
		wg.setWorkgroupId(rs.getString("workgroup_id"));
		wg.setWorkgroupName(rs.getString("workgroup_name"));
		wg.setWorkgroupOrderId(new Long(rs.getString("workgroup_order_id")));
		return wg;
	}

	private Structurebaseinfo getStructurebaseinfo(ResultSet rs)
			throws Exception {
		Structurebaseinfo sbi = new Structurebaseinfo();
		sbi.setElementId(rs.getString("element_id"));
		sbi.setElementType(rs.getString("element_type"));
		sbi.setElementName(rs.getString("element_name"));
		sbi.setElementDesc(rs.getString("element_desc"));
		sbi.setElementOrder(Integer.valueOf(rs.getInt("element_order")));
		sbi.setElementParentId(rs.getString("element_parent_id"));
		return sbi;
	}

	/**
	 * Description: 获取用户信息
	 * 
	 */
	private Map getAuthInfo(String sql, String dn, String username, String pwd) {
		if (this.conn == null) {
			return null;
		}
		Map map = null;
		try {
			this.pre = this.conn.prepareStatement(sql);
			if ((dn != null) && (!dn.equals(""))) {
				this.pre.setString(1, dn);
			} else {
				this.pre.setString(1, username);
				this.pre.setString(2, pwd);
			}
			this.rs = this.pre.executeQuery();

			if (rs != null && rs.next()) {
				map = new HashMap();
				String userid = this.rs.getString("uuid");
				String uname = this.rs.getString("uname");
				String ucode = this.rs.getString("ucode");
				String usid = this.rs.getString("usid");
				String uaid = this.rs.getString("uaid");
				String pid = this.rs.getString("pid");
				String pname = this.rs.getString("pname");
				String isleader = this.rs.getString("isleader");
				String isec = this.rs.getString("isec");
				String otel = this.rs.getString("otel");
				String handset = this.rs.getString("handset");
				String ppid = this.rs.getString("ppid");
				String scod = this.rs.getString("pscode");
				String ccod = this.rs.getString("pccode");
				String dtype = this.rs.getString("dtype");
				String pwds = this.rs.getString("pwd");
				String loginName = this.rs.getString("username");

				map.put("userid", userid);
				map.put("username", uname);
				map.put("structureid", usid);
				map.put("structurecode", ucode);
				map.put("allparentid", uaid);
				map.put("depid", pid);
				map.put("depname", pname);
				map.put("isLeader", isleader);
				map.put("isSec", isec);
				map.put("officeTel", otel);
				map.put("handset", handset);
				map.put("pwd", pwds);
				map.put("loginname", loginName);
				String ifDepId = "";
				String ifDepName = "";
				if (dtype.equals("1")) {
					ifDepId = pid;
					ifDepName = pname;
				}
				closeRs();
				sql = "select st.element_id pid,st.element_name pname from structure st,department dep where  st.element_id=?";
				this.pre = this.conn.prepareStatement(sql);
				this.pre.setString(1, ppid);
				this.rs = this.pre.executeQuery();

				boolean result = false;

				if ((this.rs != null) && (this.rs.next())) {
					String pdepid = this.rs.getString("pid");
					String pdepname = this.rs.getString("pname");
					map.put("pdepid", pdepid);
					map.put("pdepname", pdepname);
					result = true;
				}
				closeRs();
				if ((scod != null) && (!scod.equals("")) && (ccod != null)
						&& (!ccod.equals("")) && (ifDepId != null))
					if (!ifDepId.equals("")) {
						while (result) {
							sql = "select st.element_parent_id ppid,st.element_id pid,st.element_name pname,dep.department_type dtype,st.structure_code pscode,st.centercode pccode from structure st left join department dep on dep.department_id=st.element_id where st.element_id =?";
							this.pre = this.conn.prepareStatement(sql);
							this.pre.setString(1, ppid);
							this.rs = this.pre.executeQuery();
							if ((this.rs != null) && (this.rs.next())) {
								ppid = this.rs.getString("ppid");
								String tempScod = this.rs.getString("pscode");
								String tempCcod = this.rs.getString("pccode");
								String tempDepType = this.rs.getString("dtype");
								String tempPid = this.rs.getString("pid");
								String tempPname = this.rs.getString("pname");
								if ((((scod == null) || (scod.equals(""))))
										&& (tempScod != null)
										&& (!tempScod.equals(""))) {
									scod = tempScod;
								}

								if ((((ccod == null) || (scod.equals(""))))
										&& (tempCcod != null)
										&& (!tempCcod.equals(""))) {
									ccod = tempCcod;
								}

								if ((((ifDepId == null) || (ifDepId.equals(""))))
										&& ((((tempDepType != null) && (tempDepType
												.equals("1"))) || (ppid
												.equals("0"))))) {
									ifDepId = tempPid;
									ifDepName = tempPname;
								}

								if ((scod != null) && (!scod.equals(""))
										&& (ccod != null) && (!ccod.equals(""))
										&& (ifDepId != null)
										&& (!ifDepId.equals("")))
									result = false;
							} else {
								result = false;
							}
							closeRs();
						}
					}
				map.put("ifDepId", ifDepId);
				map.put("ifDepName", ifDepName);
				map.put("structurecode", scod);
				map.put("centercode", ccod);
			}
		} catch (Exception e) {
			log.error("获取用户信息异常", e);
		} finally {
			closeRs();
		}

		return map;
	}
	/**
	 * 封装工作组对象
	 * 
	 * @param ResultSet
	 * @return Groupinfo
	 * @throws Exception
	 */
	private Workgroup workgroup(ResultSet rs) throws Exception {
		Workgroup wg = new Workgroup();
		wg.setStructureId(rs.getString("structure_Id"));
		wg.setWorkgroupCode(rs.getString("workgroup_Code"));
		wg.setWorkgroupDesc(rs.getString("workgroup_Desc"));
		wg.setWorkgroupId(rs.getString("workgroup_Id"));
		wg.setWorkgroupName(rs.getString("workgroup_Name"));
		wg.setWorkgroupOrderId(rs.getLong("workgroup_Order_Id"));
		return wg;
	}

	/**
	 * 封装群组对象
	 * 
	 * @param ResultSet
	 * @return Groupinfo
	 * @throws Exception
	 */
	private Groupinfo getGroupinfo(ResultSet rs) throws Exception {
		Groupinfo gi = new Groupinfo();
		gi.setCode(rs.getString("code"));
		gi.setCountType(rs.getString("count_type"));
		gi.setGroupDesc(rs.getString("group_desc"));
		gi.setGroupName(rs.getString("group_name"));
		gi.setGroupOrder(Integer.valueOf(Integer.parseInt(rs
				.getString("group_order"))));
		gi.setId(rs.getString("id"));
		gi.setSqlStr(rs.getString("sql_str"));
		gi.setTypeName(rs.getString("type_name"));
		gi.setTypeValue(rs.getString("type_value"));
		return gi;
	}

	private String getDatabseType() {
		Properties pps = new Properties();

		try {
			InputStream inputStream = this.getClass().getClassLoader()
					.getResourceAsStream("basedb.conf");
			pps.load(inputStream);
			String strValue = pps.getProperty("databasetype");
			return strValue;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "oracle";
	}
	
	public int setAdElement(String element_id,String code,String value) throws Exception {
		if (this.conn == null) {
			throw new Exception("数据库连接为空");
		}
		if (element_id == null) {
			throw new Exception("标识element_id为null");
		}
		Map map = new HashMap();
		int result=0;
		try {
			String sql = "select adid,adtype from attributedefined where adcode=?";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, code);
			this.rs = this.pre.executeQuery();
			rs.next();
			
			String adtype = rs.getString(2);
			String adid =rs.getString(1);
			closeRs();
			if ("1".equals(adtype) || "3".equals(adtype)
					|| "5".equals(adtype) || "6".equals(adtype))
				sql="insert into attributevalue(atid,adid,elementid,strvalue) values(sys_guid(),?,?,?)";
			if ("2".equals(adtype))
				sql="insert into attributevalue(atid,adid,elementid,clobvalue) values(sys_guid(),?,?,?)";
			if ("4".equals(adtype))
				sql="insert into attributevalue(atid,adid,elementid,datevalue) values(sys_guid(),?,?,to_date(?,'yyyy-MM-dd'))";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1,adid);
			this.pre.setString(2,element_id);
			this.pre.setString(3, value);
			result=this.pre.executeUpdate();
		} catch (Exception e) {
			log.error("通过elementid获取扩展属性列表异常1", e);
		} finally {
			closeRs();
		}
		return result;
	}
	
	/**
	 * Description: 取得应用下角色列表
	 * 
	 * @param appCode jigouId 机构id
	 *            应用代码
	 * @return 资源列表  List中保存的是Roleinfo权限对象
	 */
	public List<Roleinfo> getRoles(String appCode,String jigouId) {
		if (this.conn == null) {
			return null;
		}
		if (appCode == null) {
			return null;
		}
		List list = null;
		try {
			String sql = "select * from Roleinfo role where role.sys_code=? and role.structure_id =?  order by role.role_order ";
			this.pre = this.conn.prepareStatement(sql);
			this.pre.setString(1, appCode);
			this.pre.setString(2, jigouId);
			this.rs = this.pre.executeQuery();
			list = new ArrayList();
			while (rs != null && rs.next()) {
				Roleinfo res = getRoleinfo(rs);
				list.add(res);
			}
		} catch (Exception e) {
			log.error("取得应用下角色列表异常", e);
		} finally {
			closeRs();
		}
		return list;
	}
}
